package com.fitnessapp.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHelper extends SQLiteOpenHelper {

	private static final String DATABASE_NAME = "user_db";
	private static final int DATABASE_VERSION = 5;
	
	//tables
	public static final String ROUTINE_TABLE = "routine";
	public static final String ROUTINE_EXERCISE_TABLE = "routine_exercise";
	public static final String USER_TABLE = "user";
	public static final String USER_ROUTINE_TABLE = "user_routine";
	public static final String EXERCISE_TABLE ="exercise";
	
	//routine table columns
	public static final String ROUTINE_TABLE_ID = "routine_id";
	public static final String ROUTINE_TABLE_AUTHOR_NAME = "routine_author_name";
	public static final String ROUTINE_TABLE_CREATION_DATE = "date_created";
	public static final String ROUTINE_TABLE_SHARED_FLAG = "is_public";
	public static final String ROUTINE_TABLE_NAME = "routine_name";
	public static final String ROUTINE_TABLE_GOAL = "routine_goal";
	
	//routine table creation string
	private static final String CREATE_ROUTINE_TABLE = "create table " +
	ROUTINE_TABLE + " (" + ROUTINE_TABLE_ID + " integer primary key autoincrement, " + 
			ROUTINE_TABLE_AUTHOR_NAME + " text not null, " +
			ROUTINE_TABLE_CREATION_DATE + " text not null, " + 
			ROUTINE_TABLE_GOAL + " text not null, " + 
			ROUTINE_TABLE_SHARED_FLAG + " integer not null, " + 
			ROUTINE_TABLE_NAME + " text unique);";
	
	
	
	//exercise table columns
	public static final String EXERCISE_TABLE_ID = "exercise_id";
	public static final String EXERCISE_TABLE_NAME = "exercise_name";
	public static final String EXERCISE_TABLE_DESCRIPTION = "exercise_desc";
	public static final String EXERCISE_TABLE_NOTES = "exercise_notes";
	public static final String EXERCISE_TABLE_SETS = "exercise_sets";
	public static final String EXERCISE_TABLE_REPS = "exercise_reps";
	
	//exercise_table creation string
	private static final String CREATE_EXERCISE_TABLE = "create table " +
			EXERCISE_TABLE + " (" + EXERCISE_TABLE_ID + " integer primary key, " + 
			EXERCISE_TABLE_NAME + " text unique, " +
			EXERCISE_TABLE_NOTES + " text not null, " +
			EXERCISE_TABLE_SETS  + " integer not null, " +
			EXERCISE_TABLE_REPS + " integer not null, " +
			EXERCISE_TABLE_DESCRIPTION + " text not null);";
	
	//routine_exercise table columns
	public static final String RE_TABLE_ROUTINE_ID = "routine_id";
	public static final String RE_TABLE_EXERCISE_ID = "exercise_id";
	public static final String RE_TABLE_DAY_OF_WEEK = "day_of_week";
	
	//routine_exercise table creation string
	private static final String CREATE_ROUTINE_EXERCISE_TABLE = "create table " +
	ROUTINE_EXERCISE_TABLE + " (" + RE_TABLE_ROUTINE_ID + " integer primary key, " + 
			RE_TABLE_EXERCISE_ID + " integer not null, " +
			RE_TABLE_DAY_OF_WEEK + " integer not null, FOREIGN KEY(" +
			RE_TABLE_ROUTINE_ID + ") REFERENCES " + ROUTINE_TABLE + "(" +
			ROUTINE_TABLE_ID + "), " +
			" FOREIGN KEY(" +
			RE_TABLE_EXERCISE_ID + ") REFERENCES " + EXERCISE_TABLE + "(" +
			EXERCISE_TABLE_ID + "));";
		
	//user table columns
	public static final String USER_TABLE_ID = "user_id";
	public static final String USER_TABLE_USERNAME = "username";
	public static final String USER_TABLE_CURRENT_ROUTINE = "current_routine_id";
	
	//user table creation string
	private static final String CREATE_USER_TABLE = "create table " +
	USER_TABLE + " (" + USER_TABLE_ID + " integer primary key autoincrement, " + 
			USER_TABLE_USERNAME + " text unique, " +
			USER_TABLE_CURRENT_ROUTINE + " integer, FOREIGN KEY(" +
			USER_TABLE_CURRENT_ROUTINE + ") REFERENCES " + ROUTINE_TABLE + "(" +
			ROUTINE_TABLE_ID + "));";
	
	
	public static final String LOCAL_USER = "local_user";	
	
	
	public DatabaseHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}
	
			
	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(CREATE_USER_TABLE);
		db.execSQL(CREATE_ROUTINE_TABLE);
		db.execSQL(CREATE_EXERCISE_TABLE);
		db.execSQL(CREATE_ROUTINE_EXERCISE_TABLE);		
		
		
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

		//I guess for now we will drop the tables and just re create the db
		db.execSQL("DROP TABLE IF EXISTS " + USER_TABLE);
		db.execSQL("DROP TABLE IF EXISTS " + ROUTINE_TABLE);
		db.execSQL("DROP TABLE IF EXISTS " + EXERCISE_TABLE);
		db.execSQL("DROP TABLE IF EXISTS " + ROUTINE_EXERCISE_TABLE);

		onCreate(db);
	}

}
